| 越晚搞懂 MySQL JSON 数据类型，你就越吃亏！ | 您所在的位置：网站首页 › mysql json 索引 效率 › 越晚搞懂 MySQL JSON 数据类型，你就越吃亏！ |
|
1、查询相关
1）JSON_CONTAINS(target, candidate[, path])
判断 target 文档是否包含 candidate 文档，如果包含，则返回 1，否则是 0。 mysql> set @j = '{"a": [1, 2], "b": 3, "c": {"d": 4}}';Query OK, 0 rows affected (0.00 sec) mysql> select json_contains(@j, '1', '$.a'),json_contains(@j, '1', '$.b');+-------------------------------+-------------------------------+| json_contains(@j, '1', '$.a') | json_contains(@j, '1', '$.b') |+-------------------------------+-------------------------------+| 1 | 0 |+-------------------------------+-------------------------------+1 row in set (0.00 sec) mysql> select json_contains(@j,'{"d": 4}','$.a'),json_contains(@j,'{"d": 4}','$.c');+------------------------------------+------------------------------------+| json_contains(@j,'{"d": 4}','$.a') | json_contains(@j,'{"d": 4}','$.c') |+------------------------------------+------------------------------------+| 0 | 1 |+------------------------------------+------------------------------------+1 row in set (0.00 sec)
2）JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
判断指定的 path 是否存在，存在，则返回 1，否则是 0。
函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。 mysql> set @j = '{"a": [1, 2], "b": 3, "c": {"d": 4}}';Query OK, 0 rows affected (0.00 sec) mysql> select json_contains_path(@j, 'one', '$.a', '$.e'), json_contains_path(@j, 'all', '$.a', '$.e');+---------------------------------------------+---------------------------------------------+| json_contains_path(@j, 'one', '$.a', '$.e') | json_contains_path(@j, 'all', '$.a', '$.e') |+---------------------------------------------+---------------------------------------------+| 1 | 0 |+---------------------------------------------+---------------------------------------------+1 row in set (0.00 sec) mysql> select json_contains_path(@j, 'one', '$.c.d'),json_contains_path(@j, 'one', '$.a.d');+----------------------------------------+----------------------------------------+| json_contains_path(@j, 'one', '$.c.d') | json_contains_path(@j, 'one', '$.a.d') |+----------------------------------------+----------------------------------------+| 1 | 0 |+----------------------------------------+----------------------------------------+1 row in set (0.00 sec)
3）JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
返回某个字符串（search_str）在 JSON 文档中的位置，其中，
one_or_all：匹配的次数，one 是只匹配一次，all 是匹配所有。如果匹配到多个，结果会以数组的形式返回。 search_str：子串，支持模糊匹配：% 和 _ 。 escape_char：转义符，如果该参数不填或为 NULL，则取默认转义符\。 path：查找路径。 mysql> set @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';Query OK, 0 rows affected (0.00 sec) mysql> select json_search(@j, 'one', 'abc'),json_search(@j, 'all', 'abc'),json_search(@j, 'all', 'ghi');+-------------------------------+-------------------------------+-------------------------------+| json_search(@j, 'one', 'abc') | json_search(@j, 'all', 'abc') | json_search(@j, 'all', 'ghi') |+-------------------------------+-------------------------------+-------------------------------+| "$[0]" | ["$[0]", "$[2].x"] | NULL |+-------------------------------+-------------------------------+-------------------------------+1 row in set (0.00 sec) mysql> select json_search(@j, 'all', '%b%', NULL, '$[1]'), json_search(@j, 'all', '%b%', NULL, '$[3]');+---------------------------------------------+---------------------------------------------+| json_search(@j, 'all', '%b%', NULL, '$[1]') | json_search(@j, 'all', '%b%', NULL, '$[3]') |+---------------------------------------------+---------------------------------------------+| NULL | "$[3].y" |+---------------------------------------------+---------------------------------------------+1 row in set (0.00 sec)
4）JSON_KEYS(json_doc[, path])
返回 JSON 文档最外层的 key，如果指定了 path，则返回该 path 对应元素最外层的 key。 mysql> select json_keys('{"a": 1, "b": {"c": 30}}');+---------------------------------------+| json_keys('{"a": 1, "b": {"c": 30}}') |+---------------------------------------+| ["a", "b"] |+---------------------------------------+1 row in set (0.00 sec) mysql> select json_keys('{"a": 1, "b": {"c": 30}}', '$.b');+----------------------------------------------+| json_keys('{"a": 1, "b": {"c": 30}}', '$.b') |+----------------------------------------------+| ["c"] |+----------------------------------------------+1 row in set (0.00 sec)
5）JSON_VALUE(json_doc, path)
8.0.21 引入的，从 JSON 文档提取指定路径（path）的元素。
该函数的完整语法如下： JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error]) on_empty: {NULL | ERROR | DEFAULT value} ON EMPTY on_error: {NULL | ERROR | DEFAULT value} ON ERROR
其中：
RETURNING type：返回值的类型，不指定，则默认是 VARCHAR(512)。不指定字符集，则默认是 utf8mb4，且区分大小写。 on_empty：如果指定路径没有值，会触发 on_empty 子句， 默认是返回 NULL，也可指定 ERROR 抛出错误，或者通过 DEFAULT value 返回默认值。 on_error：三种情况下会触发 on_error 子句：从数组或对象中提取元素时，会解析到多个值；类型转换错误，譬如将 "abc" 转换为 unsigned 类型；值被 truncate 了。默认是返回 NULL。 mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item');+-------------------------------------------------------------+| json_value('{"item": "shoes", "price": "49.95"}', '$.item') |+-------------------------------------------------------------+| shoes |+-------------------------------------------------------------+1 row in set (0.00 sec) mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price' returning decimal(4,2)) as price;+-------+| price |+-------+| 49.95 |+-------+1 row in set (0.00 sec) mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price1' error on empty);ERROR 3966 (22035): No value was found by 'json_value' on the specified path. mysql> select json_value('[1, 2, 3]', '$[1 to 2]' error on error);ERROR 3967 (22034): More than one value was found by 'json_value' on the specified path. mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item' returning unsigned error on error) as price;ERROR 1690 (22003): UNSIGNED value is out of range in 'json_value'
6）value MEMBER OF(json_array)
判断 value 是否是 JSON 数组的一个元素，如果是，则返回 1，否则是 0。 mysql> select 17 member of('[23, "abc", 17, "ab", 10]');+-------------------------------------------+| 17 member of('[23, "abc", 17, "ab", 10]') |+-------------------------------------------+| 1 |+-------------------------------------------+1 row in set (0.00 sec) mysql> select cast('[4,5]' as json) member of('[[3,4],[4,5]]');+--------------------------------------------------+| cast('[4,5]' as json) member of('[[3,4],[4,5]]') |+--------------------------------------------------+| 1 |+--------------------------------------------------+1 row in set (0.00 sec)
7）JSON_OVERLAPS(json_doc1, json_doc2)
MySQL 8.0.17 引入的，用来比较两个 JSON 文档是否有相同的键值对或数组元素，如果有，则返回 1，否则是 0。如果两个参数都是标量，则判断这两个标量是否相等。 mysql> select json_overlaps('[1,3,5,7]', '[2,5,7]'),json_overlaps('[1,3,5,7]', '[2,6,8]');+---------------------------------------+---------------------------------------+| json_overlaps('[1,3,5,7]', '[2,5,7]') | json_overlaps('[1,3,5,7]', '[2,6,8]') |+---------------------------------------+---------------------------------------+| 1 | 0 |+---------------------------------------+---------------------------------------+1 row in set (0.00 sec) mysql> select json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":2}');+-------------------------------------------------------+| json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":2}') |+-------------------------------------------------------+| 1 |+-------------------------------------------------------+1 row in set (0.00 sec) mysql> select json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":10}');+--------------------------------------------------------+| json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":10}') |+--------------------------------------------------------+| 0 |+--------------------------------------------------------+1 row in set (0.00 sec) mysql> select json_overlaps('5', '5'),json_overlaps('5', '6');+-------------------------+-------------------------+| json_overlaps('5', '5') | json_overlaps('5', '6') |+-------------------------+-------------------------+| 1 | 0 |+-------------------------+-------------------------+1 row in set (0.00 sec)
从 MySQL 8.0.17 开始，InnoDB 支持多值索引，可用在 JSON 数组中。当我们使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 进行数组相关的操作时，可使用多值索引来加快查询。 2、修改相关
1）JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
向数组指定位置追加元素。如果指定 path 不存在，则不添加。 mysql> set @j = '["a", ["b", "c"], "d"]';Query OK, 0 rows affected (0.00 sec) mysql> select json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3);+-----------------------------------------------------------+| json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3) |+-----------------------------------------------------------+| [["a", 1], [["b", 2], "c"], "d"] |+-----------------------------------------------------------+1 row in set (0.00 sec) mysql> set @j = '{"a": 1, "b": [2, 3], "c": 4}';Query OK, 0 rows affected (0.00 sec) mysql> select json_array_append(@j, '$.b', 'x', '$', 'z');+---------------------------------------------+| json_array_append(@j, '$.b', 'x', '$', 'z') |+---------------------------------------------+| [{"a": 1, "b": [2, 3, "x"], "c": 4}, "z"] |+---------------------------------------------+1 row in set (0.00 sec)
2）JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
向数组指定位置插入元素。 mysql> set @j = '["a", ["b", "c"],{"d":"e"}]';Query OK, 0 rows affected (0.00 sec) mysql> select json_array_insert(@j, '$[0]', 1);+----------------------------------+| json_array_insert(@j, '$[0]', 1) |+----------------------------------+| [1, "a", ["b", "c"], {"d": "e"}] |+----------------------------------+1 row in set (0.00 sec) mysql> select json_array_insert(@j, '$[1]', cast('[1,2]' as json));+------------------------------------------------------+| json_array_insert(@j, '$[1]', cast('[1,2]' as json)) |+------------------------------------------------------+| ["a", [1, 2], ["b", "c"], {"d": "e"}] |+------------------------------------------------------+1 row in set (0.00 sec) mysql> select json_array_insert(@j, '$[5]', 2);+----------------------------------+| json_array_insert(@j, '$[5]', 2) |+----------------------------------+| ["a", ["b", "c"], {"d": "e"}, 2] |+----------------------------------+1 row in set (0.00 sec)
3）JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)
MySQL 8.0.3 引入的，用来合并多个 JSON 文档。其合并规则如下：
如果两个文档不全是 JSON 对象，则合并后的结果是第二个文档。 如果两个文档都是 JSON 对象，且不存在着同名 KEY，则合并后的文档包括两个文档的所有元素，如果存在着同名 KEY，则第二个文档的值会覆盖第一个。 mysql> select json_merge_patch('[1, 2]', '[3, 4]'), json_merge_patch('[1, 2]', '{"a": 123}');+--------------------------------------+------------------------------------------+| json_merge_patch('[1, 2]', '[3, 4]') | json_merge_patch('[1, 2]', '{"a": 123}') |+--------------------------------------+------------------------------------------+| [3, 4] | {"a": 123} |+--------------------------------------+------------------------------------------+1 row in set (0.00 sec) mysql> select json_merge_patch('{"a": 1}', '{"b": 2}'),json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');+------------------------------------------+-----------------------------------------------------------+| json_merge_patch('{"a": 1}', '{"b": 2}') | json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |+------------------------------------------+-----------------------------------------------------------+| {"a": 1, "b": 2} | {"a": 3, "b": 2, "c": 4} |+------------------------------------------+-----------------------------------------------------------+1 row in set (0.00 sec) # 如果第二个文档存在 null 值，文档合并后不会输出对应的 KEY。mysql> select json_merge_patch('{"a":1, "b":2}', '{"a":3, "b":null}');+---------------------------------------------------------+| json_merge_patch('{"a":1, "b":2}', '{"a":3, "b":null}') |+---------------------------------------------------------+| {"a": 3} |+---------------------------------------------------------+1 row in set (0.00 sec)
4）JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)
MySQL 8.0.3 引入的，用来代替 JSON_MERGE。也是用来合并文档，但合并规则与 JSON_MERGE_PATCH 有所不同。
两个文档中，只要有一个文档是数组，则另外一个文档会合并到该数组中。 两个文档都是 JSON 对象，若存在着同名 KEY ，第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。 mysql> select json_merge_preserve('1','2'),json_merge_preserve('[1, 2]', '[3, 4]');+------------------------------+-----------------------------------------+| json_merge_preserve('1','2') | json_merge_preserve('[1, 2]', '[3, 4]') |+------------------------------+-----------------------------------------+| [1, 2] | [1, 2, 3, 4] |+------------------------------+-----------------------------------------+1 row in set (0.00 sec) mysql> select json_merge_preserve('[1, 2]', '{"a": 123}'), json_merge_preserve('{"a": 123}', '[3,4]');+---------------------------------------------+--------------------------------------------+| json_merge_preserve('[1, 2]', '{"a": 123}') | json_merge_preserve('{"a": 123}', '[3,4]') |+---------------------------------------------+--------------------------------------------+| [1, 2, {"a": 123}] | [{"a": 123}, 3, 4] |+---------------------------------------------+--------------------------------------------+1 row in set (0.00 sec) mysql> select json_merge_preserve('{"a": 1}', '{"b": 2}'), json_merge_preserve('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');+---------------------------------------------+--------------------------------------------------------------+| json_merge_preserve('{"a": 1}', '{"b": 2}') | json_merge_preserve('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |+---------------------------------------------+--------------------------------------------------------------+| {"a": 1, "b": 2} | {"a": [1, 3], "b": 2, "c": 4} |+---------------------------------------------+--------------------------------------------------------------+1 row in set (0.00 sec)
5）JSON_MERGE(json_doc, json_doc[, json_doc] ...)
与 JSON_MERGE_PRESERVE 作用一样，从 MySQL 8.0.3 开始不建议使用，后续会移除。 3、其它辅助函数
1）JSON_QUOTE(string)
生成有效的 JSON 字符串，主要是对一些特殊字符（如双引号）进行转义。 mysql> select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');+--------------------+----------------------+-------------------------+| json_quote('null') | json_quote('"null"') | json_quote('[1, 2, 3]') |+--------------------+----------------------+-------------------------+| "null" | "\"null\"" | "[1, 2, 3]" |+--------------------+----------------------+-------------------------+1 row in set (0.00 sec)
除此之外，也可通过 CAST(value AS JSON) 进行类型转换。
2）JSON_UNQUOTE(json_val)
将 JSON 转义成字符串输出。 mysql> select c2->'$.ename',json_unquote(c2->'$.ename'), -> json_valid(c2->'$.ename'),json_valid(json_unquote(c2->'$.ename')) from t;+---------------+-----------------------------+---------------------------+-----------------------------------------+| c2->'$.ename' | json_unquote(c2->'$.ename') | json_valid(c2->'$.ename') | json_valid(json_unquote(c2->'$.ename')) |+---------------+-----------------------------+---------------------------+-----------------------------------------+| "jack" | jack | 1 | 0 || "mark" | mark | 1 | 0 |+---------------+-----------------------------+---------------------------+-----------------------------------------+2 rows in set (0.00 sec)
直观地看，没加 JSON_UNQUOTE 字符串会用双引号引起来，加了 JSON_UNQUOTE 就没有。但本质上，前者是 JSON 中的 STRING 类型，后者是 MySQL 中的字符类型，这一点可通过 JSON_VALID 来判断。
3）JSON_OBJECTAGG(key, value)
取表中的两列作为参数，其中，第一列是 key，第二列是 value，返回 JSON 对象。如， mysql> select * from emp;+--------+----------+--------+| deptno | ename | sal |+--------+----------+--------+| 10 | emp_1001 | 100.00 || 10 | emp_1002 | 200.00 || 20 | emp_1003 | 300.00 || 20 | emp_1004 | 400.00 |+--------+----------+--------+4 rows in set (0.00 sec) mysql> select json_objectagg(ename,sal) from emp;+----------------------------------------------------------------------------------+| json_objectagg(ename,sal) |+----------------------------------------------------------------------------------+| {"emp_1001": 100.00, "emp_1002": 200.00, "emp_1003": 300.00, "emp_1004": 400.00} |+----------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> select deptno,json_objectagg(ename,sal) from emp group by deptno;+--------+------------------------------------------+| deptno | json_objectagg(ename,sal) |+--------+------------------------------------------+| 10 | {"emp_1001": 100.00, "emp_1002": 200.00} || 20 | {"emp_1003": 300.00, "emp_1004": 400.00} |+--------+------------------------------------------+2 rows in set (0.00 sec)
4）JSON_ARRAYAGG(col_or_expr)
将列的值聚合成 JSON 数组，注意，JSON 数组中元素的顺序是随机的。 mysql> select json_arrayagg(ename) from emp;+--------------------------------------------------+| json_arrayagg(ename) |+--------------------------------------------------+| ["emp_1001", "emp_1002", "emp_1003", "emp_1004"] |+--------------------------------------------------+1 row in set (0.00 sec) mysql> select deptno,json_arrayagg(ename) from emp group by deptno;+--------+--------------------------+| deptno | json_arrayagg(ename) |+--------+--------------------------+| 10 | ["emp_1001", "emp_1002"] || 20 | ["emp_1003", "emp_1004"] |+--------+--------------------------+2 rows in set (0.00 sec)
5）JSON_PRETTY(json_val)
将 JSON 格式化输出。 mysql> select json_pretty("[1,3,5]");+------------------------+| json_pretty("[1,3,5]") |+------------------------+| [ 1, 3, 5] |+------------------------+1 row in set (0.00 sec) mysql> select json_pretty('{"a":"10","b":"15","x":"25"}');+---------------------------------------------+| json_pretty('{"a":"10","b":"15","x":"25"}') |+---------------------------------------------+| { "a": "10", "b": "15", "x": "25"} |+---------------------------------------------+1 row in set (0.00 sec)
6）JSON_STORAGE_FREE(json_val)
MySQL 8.0 新增的，与 Partial Updates 有关，用于计算 JSON 文档在进行部分更新后的剩余空间。
7）JSON_STORAGE_SIZE(json_val)
MySQL 5.7.22 引入的，用于计算 JSON 文档的空间使用情况。
8）JSON_DEPTH(json_doc)
返回 JSON 文档的最大深度。对于空数组，空对象，标量值，其深度为 1。 mysql> select json_depth('{}'),json_depth('[10, 20]'),json_depth('[10, {"a": 20}]');+------------------+------------------------+-------------------------------+| json_depth('{}') | json_depth('[10, 20]') | json_depth('[10, {"a": 20}]') |+------------------+------------------------+-------------------------------+| 1 | 2 | 3 |+------------------+------------------------+-------------------------------+1 row in set (0.00 sec)
9）JSON_LENGTH(json_doc[, path])
返回 JSON 文档的长度，其计算规则如下：
如果是标量值，其长度为 1。 如果是数组，其长度为数组元素的个数。 如果是对象，其长度为对象元素的个数。 不包括嵌套数据和嵌套对象的长度。 mysql> select json_length('"abc"');+----------------------+| json_length('"abc"') |+----------------------+| 1 |+----------------------+1 row in set (0.00 sec) mysql> select json_length('[1, 2, {"a": 3}]');+---------------------------------+| json_length('[1, 2, {"a": 3}]') |+---------------------------------+| 3 |+---------------------------------+1 row in set (0.00 sec) mysql> select json_length('{"a": 1, "b": {"c": 30}}');+-----------------------------------------+| json_length('{"a": 1, "b": {"c": 30}}') |+-----------------------------------------+| 2 |+-----------------------------------------+1 row in set (0.00 sec) mysql> select json_length('{"a": 1, "b": {"c": 30}}', '$.a');+------------------------------------------------+| json_length('{"a": 1, "b": {"c": 30}}', '$.a') |+------------------------------------------------+| 1 |+------------------------------------------------+1 row in set (0.00 sec)
10）JSON_TYPE(json_val)
返回 JSON 值的类型。 mysql> select json_type('123');+------------------+| json_type('123') |+------------------+| INTEGER |+------------------+1 row in set (0.00 sec) mysql> select json_type('"abc"');+--------------------+| json_type('"abc"') |+--------------------+| STRING |+--------------------+1 row in set (0.00 sec) mysql> select json_type(cast(now() as json));+--------------------------------+| json_type(cast(now() as json)) |+--------------------------------+| DATETIME |+--------------------------------+1 row in set (0.00 sec) mysql> select json_type(json_extract('{"a": [10, true]}', '$.a'));+-----------------------------------------------------+| json_type(json_extract('{"a": [10, true]}', '$.a')) |+-----------------------------------------------------+| ARRAY |+-----------------------------------------------------+1 row in set (0.00 sec)
11）JSON_VALID(val)
判断给定值是否是有效的 JSON 文档。 mysql> select json_valid('hello'), json_valid('"hello"');+---------------------+-----------------------+| json_valid('hello') | json_valid('"hello"') |+---------------------+-----------------------+| 0 | 1 |+---------------------+-----------------------+1 row in set (0.00 sec)
12）JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
从 JSON 文档中提取数据并以表格的形式返回。
该函数的完整语法如下： JSON_TABLE( expr, path COLUMNS (column_list)) [AS] alias column_list: column[, column][, ...] column: name FOR ORDINALITY | name type PATH string_path [on_empty] [on_error] | name type EXISTS PATH string_path | NESTED [PATH] path COLUMNS (column_list) on_empty: {NULL | DEFAULT json_string | ERROR} ON EMPTY on_error: {NULL | DEFAULT json_string | ERROR} ON ERROR
其中，
expr：可以返回 JSON 文档的表达式。可以是一个标量（ JSON 文档 ），列名或者一个函数调用（ JSON_EXTRACT(t1.json_data,'$.post.comments') ）。 path：JSON 的路径表达式， column：列的类型，支持以下四种类型： name FOR ORDINALITY：序号。name 是列名。 name type PATH string_path [on_empty] [on_error]：提取指定路径（ string_path ）的元素。name 是列名，type 是 MySQL 中的数据类型。 name type EXISTS PATH string_path：指定路径（ string_path ）的元素是否存在。 NESTED [PATH] path COLUMNS (column_list)：将嵌套对象或数组与来自父对象或数组的 JSON 值扁平化为一行输出。 select * from json_table( '[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]', "$[*]" columns( id for ordinality, xval varchar(100) path "$.x", yval varchar(100) path "$.y", z_exist int exists path "$.z", nested path '$.b[*]' columns (b INT PATH '$') ) ) as t;+------+------+------+---------+------+| id | xval | yval | z_exist | b |+------+------+------+---------+------+| 1 | 2 | 8 | 1 | 1 || 1 | 2 | 8 | 1 | 2 || 1 | 2 | 8 | 1 | 3 || 2 | 3 | 7 | 0 | NULL || 3 | 4 | 6 | 1 | NULL |+------+------+------+---------+------+5 rows in set (0.00 sec)
13）JSON_SCHEMA_VALID(schema,document)
判断 document （ JSON 文档 ）是否满足 schema （ JSON 对象）定义的规范要求。完整的规范要求可参考 Draft 4 of the JSON Schema specification （https://json-schema.org/specification-links.html#draft-4）。如果不满足，可通过 JSON_SCHEMA_VALIDATION_REPORT() 获取具体的原因。
以下面这个 schema 为例。 set @schema = '{ "type": "object", "properties": { "latitude": { "type": "number", "minimum": -90, "maximum": 90 }, "longitude": { "type": "number", "minimum": -180, "maximum": 180 } }, "required": ["latitude", "longitude"]}';
它的要求如下：
document 必须是 JSON 对象。 JSON 对象必需的两个属性是 latitude 和 longitude。 latitude 和 longitude 必须是数值类型，且两者的大小分别在 -90 ～ 90，-180 ～ 180 之间。
下面通过具体的 document 来测试一下。 mysql> set @document = '{"latitude": 63.444697,"longitude": 10.445118}';Query OK, 0 rows affected (0.00 sec) mysql> select json_schema_valid(@schema, @document);+---------------------------------------+| json_schema_valid(@schema, @document) |+---------------------------------------+| 1 |+---------------------------------------+1 row in set (0.00 sec) mysql> set @document = '{"latitude": 63.444697}';Query OK, 0 rows affected (0.00 sec) mysql> select json_schema_valid(@schema, @document);+---------------------------------------+| json_schema_valid(@schema, @document) |+---------------------------------------+| 0 |+---------------------------------------+1 row in set (0.00 sec) mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G*************************** 1. row ***************************json_pretty(json_schema_validation_report(@schema, @document)): { "valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}1 row in set (0.00 sec) mysql> set @document = '{"latitude": 91,"longitude": 0}';Query OK, 0 rows affected (0.00 sec) mysql> select json_schema_valid(@schema, @document);+---------------------------------------+| json_schema_valid(@schema, @document) |+---------------------------------------+| 0 |+---------------------------------------+1 row in set (0.00 sec) mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G*************************** 1. row ***************************json_pretty(json_schema_validation_report(@schema, @document)): { "valid": false, "reason": "The JSON document location '#/latitude' failed requirement 'maximum' at JSON Schema location '#/properties/latitude'", "schema-location": "#/properties/latitude", "document-location": "#/latitude", "schema-failed-keyword": "maximum"}1 row in set (0.00 sec)
|
| CopyRight 2018-2019 实验室设备网 版权所有 |